drop proc p280_p006
go
create procedure p280_p006
/*
  -----------------------------------------------------------------------
      P280_P006 - Inventory Analysis Report
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @bpd char(6)   = " " 
 , @epd char(6)   = " "
 , @loc char(3)   = " "
 , @div char(3)   = " " 
 , @dpt char(4)   = " "
 , @typ char(10)  = " "
 , @src char(6)   = " "
 , @stg char(3)   = " "
 , @fop char(6)   = " "
 , @afm char(3)   = " "
 , @srt char(15)  = " "
 , @act char(6)   = " "
 , @dev char(15)  = " "
 , @spc char(8)   = " "
 , @exp char(20)  = "01. None"
 , @ord char(25)  = "01. Category"
 , @grp char(30)  = " "
 , @dvn char(30)  = " "
 , @bus char(30)  = " "
 , @amt decimal(15,2) = 0
 , @glv char(3)   = " "
 , @prof char(50) = " "
 , @user char(8)  = " "
 , @lot  char(16) = " "

with recompile
as
/*
    ------ INTERNAL VARIABLES ------
*/

/************************************************************************
   If an Inventory Type was entered get the Inventory Type Code from the
   d280db1.dbo.t280type table to use in the report selection WHERE stmts.
 ************************************************************************/

declare @inv char(4) 
      , @date1 datetime
      , @date2 datetime
      , @diff  decimal(15)

select @date1 = getdate()


select @inv = @typ
 
if @typ != 'ALL' and @typ != ' '
   select @inv =
   ( select f_invtyp_c from d280db1.dbo.t280type 	
     where f_invtyp_x = @typ)


/************************************************************************
   Create a Temporary Table #t280_fypd to hold the Summarized values for 
   Qty and Amt for the two selected periods, Grouped by Report Type.   
 ************************************************************************/

create table #t280_fypd	
( f_invtyp_c   char(14) null
, f_invtypnm_x char(30) null
, f_inv1_q     money null
, f_inv1_a     money null
, f_inv2_q     money null 
, f_inv2_a     money null
)      


/************************************************************************
   Create a Temporary Table #t280_sum to hold the Summarized values  
   Grouped by the Report Type.  The values calculated are LEFT QTY, LEFT 
   AMT, RIGHT QTY, RIGHT AMT, IMPACT QTY, IMPACT AMT, IMPACT QTY %, IMPACT
   AMT %.
 ************************************************************************/

create table #t280_sum 	
( f_invtyp_c   char(14) null
, f_invtypnm_x char(30) null
, f_inv1_q     money null
, f_inv1_a     money null
, f_inv2_q     money null 
, f_inv2_a     money null
, f_inv3_q     money null 
, f_inv3_a     money null
, f_inv4_q     money null 
, f_inv4_a     money null
)      
/*
    ------     MAIN LOGIC     ------
*/

/************************************************************************
   DEFAULT all of the variables to the SQL Wildcard "%" for values of	
   'ALL' and ' '.
 ************************************************************************/

if @loc = "ALL" select @loc = '%'
if @div = "ALL" select @div = '%'
if @dpt = "ALL" select @dpt = '%'
if @inv = "ALL" select @inv = '%'
if @src = "ALL" select @src = '%'
if @stg = "ALL" select @stg = '%'
if @fop = "ALL" select @fop = '%'
if @afm = "ALL" select @afm = '%'
if @act = "ALL" select @act = '%'
if @dev = "ALL" select @dev = '%'
if @spc = "ALL" select @spc = '%'
if @exp = "ALL" select @exp = '%'
if @grp = "ALL" select @grp = '%'
if @dvn = "ALL" select @dvn = '%'
if @bus = "ALL" select @bus = '%'
if @lot = "ALL" select @lot = '%'
if @glv = "ALL" select @glv = '%'

if @loc = " " select @loc = '%'
if @div = " " select @div = '%'
if @dpt = " " select @dpt = '%'
if @inv = " " select @inv = '%'
if @src = " " select @src = '%'
if @stg = " " select @stg = '%'
if @fop = " " select @fop = '%'
if @afm = " " select @afm = '%'
if @act = " " select @act = '%'
if @dev = " " select @dev = '%'
if @spc = " " select @spc = '%'
if @exp = " " select @exp = '%'
if @grp = " " select @grp = '%'
if @dvn = " " select @dvn = '%'
if @bus = " " select @bus = '%'
if @lot = " " select @lot = '%'
if @glv = " " select @glv = '%'
if @glv = "A" select @glv = '%'


/************************************************************************
   If the SYSTEM PROFILE is entered you must retrieve the information
   from the pre-calculated report table (d280db1.dbo.t280rpt).
 ************************************************************************/

if @prof > ' '
and @user = 'SYSTEM'
   goto ReportProfile


/************************************************************************
   If a family is entered reset the GRP/DVN/BUS values.
 ************************************************************************/
if @afm != '%'
   begin
      select @grp = '%'
      select @dvn = '%'
      select @bus = '%'
   end

/************************************************************************
   If the report is ordered by LOT NUMBER or if a LOT Number is entered
   you must retrieve the information from the LOT level detail table
   (d280db1.dbo.t280lot).
 ************************************************************************/

if @srt = 'LOT NUMBER' or @lot != '%'
   goto ReportByLot   


/************************************************************************
   If an ACCTCD or DEVICE is entered, or if ACCTCD, DEVICE, or DEVICE/SPEC
   is chosen as the Sort By value you must select from the detail table
   containing Device information (d280db1.dbo.t280dev), instead of the
   Summary table by accounting family (d280db1.dbo.t280sum).
 ************************************************************************/


if @act != '%'
or @dev != '%'
or @srt = 'DEVICE' 
or @srt = 'DEVICE/SPEC' 
or @srt = 'ACCTCD' 
   goto ReportByDevice


/************************************************************************
   If a Business Group, Division, or Business Unit is entered or chosen   
   as the Sort By value a call is made to Procedure p280_p006b to join in
   the organization table (d280db1.dbo.t280org), else, procedure p280_p006a
   is called for summary reporting.
 ************************************************************************/

if @grp != '%'
or @dvn != '%'
or @bus != '%'
or @srt = 'BUS  GROUP' 
or @srt = 'BUS DIVISION'
or @srt = 'BUS UNIT'
   goto ReportByGrpDivBus


RptBySummary:

/************************************************************************
   Create the Summary Report
 ************************************************************************/

begin
exec p280_p006a
   @bpd 
 , @epd
 , @loc 
 , @div
 , @dpt
 , @inv
 , @src
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @glv 
end
  goto REPORT


ReportByGrpDivBus:

/************************************************************************
   Create the Summary Report using the Organization Structure
 ************************************************************************/
begin
exec p280_p006b
   @bpd 
 , @epd
 , @loc 
 , @div
 , @dpt
 , @inv
 , @src
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @grp 
 , @dvn 
 , @bus 
 , @glv 
end
  goto REPORT


ReportByDevice:

/************************************************************************
   Create the Detail Report
 ************************************************************************/

/************************************************************************
   If a Business Group, Division, or Business Unit is entered or chosen   
   as the Sort By value a call is made to Procedure p280_p006h to join in
   the organization table (d280db1.dbo.t280org), else, procedure p280_p006c
   is called for device level reporting.
 ************************************************************************/

if @srt = 'BUS  GROUP' 
or @srt = 'BUS DIVISION'
or @srt = 'BUS UNIT'
   goto ReportByDevGrpDivBus

if @afm != '%'
   begin
      select @grp = '%'
      select @dvn = '%'
      select @bus = '%'
   end

if @grp != '%'
or @dvn != '%'
or @bus != '%'
   goto ReportByDevGrpDivBus

begin
exec p280_p006c
   @bpd 
 , @epd
 , @loc 
 , @div
 , @dpt
 , @inv
 , @src
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @act
 , @dev 
 , @spc 
 , @exp 
 , @ord 
 , @grp 
 , @dvn 
 , @bus 
 , @glv 
end

  goto REPORT

ReportByDevGrpDivBus:

begin
exec p280_p006h
   @bpd 
 , @epd
 , @loc 
 , @div
 , @dpt
 , @inv
 , @src
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @act
 , @dev 
 , @spc 
 , @exp 
 , @ord 
 , @grp 
 , @dvn 
 , @bus 
 , @glv 
end

  goto REPORT


ReportByLot:   

/************************************************************************
   Create the LOT NUMBER Detail Report
 ************************************************************************/

begin
exec p280_p006e
   @bpd 
 , @epd
 , @loc 
 , @div
 , @dpt
 , @inv
 , @src
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @act
 , @dev 
 , @spc 
 , @exp 
 , @ord 
 , @grp 
 , @dvn 
 , @bus 
 , @glv 
 , @lot 
end

  goto REPORT



ReportProfile:

/************************************************************************
   Procedure p280_p006f will create the Report from the profile report table.
 ************************************************************************/

begin
exec p280_p006f
   @exp 
 , @ord 
 , @amt 
 , @prof
 , @user
end
           
   goto ENDIT    


REPORT:

/************************************************************************
   Procedure p280_p006d will create the Report from the temporary tables.    
 ************************************************************************/

begin
exec p280_p006d
   @exp 
 , @ord 
 , @amt 
end
           
   goto ENDIT    


ENDIT: 

/************************************************************************
   Procedure p280_p006g will insert an audit trail record into t280audit.   
 ************************************************************************/

/*goto EOJ */

select @date2 = getdate()
select @diff = datediff(millisecond,@date1,@date2)

begin
exec p280_p006g
   @bpd 
 , @epd 
 , @loc
 , @div
 , @dpt
 , @typ
 , @src 
 , @stg 
 , @fop 
 , @afm 
 , @srt 
 , @act 
 , @dev 
 , @spc 
 , @exp 
 , @ord 
 , @grp 
 , @dvn 
 , @bus
 , @amt 
 , @glv
 , @prof
 , @user 
 , @diff 

end

EOJ:

/************************************************************************
   Return to the calling environment
 ************************************************************************/

return ( 0 )
go
